package com.jyhs.maven.DAO.impl;

import com.jyhs.maven.DAO.BaseDao;
import com.jyhs.maven.DAO.xtyhDao;
import com.jyhs.maven.entity.Xtyh;
import com.jyhs.maven.entity.Zzjl;
import com.jyhs.maven.util.ReliesUtil;

import java.sql.PreparedStatement;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class xtyhDaoImpl extends BaseDao implements xtyhDao {

    @Override
    public List<Xtyh> getXtyhDao() throws SQLException, IllegalAccessException {
        getBeat();
        String sql = "select * from xtyh";
        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        List<Xtyh> list = new ArrayList<>();
        while (rs.next()) {
            Xtyh xtyh = new Xtyh();
            ReliesUtil.rels(rs,xtyh);
//            xtyh.setId(rs.getInt("ID"));
//            xtyh.setName(rs.getString("name"));
//            xtyh.setBcm(rs.getString("Bcm"));
//            xtyh.setMph(rs.getString("Mph"));
//            xtyh.setBalance(rs.getString("balance"));
            list.add(xtyh);
        }
        return list;
    }

    @Override
    public int addXtyhDao(Zzjl zzjl) throws SQLException, IllegalAccessException {
        getBeat();
        String sql = "INSERT INTO b.zzjl (Fname,Sname,Smcp,Ttime,Zb) VALUES (?,?,?,NOW(),?);";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, zzjl.getFname());
        ps.setString(2, zzjl.getSname());
        ps.setString(3, zzjl.getSmcp());
        ps.setString(4, zzjl.getZb());
        ps.executeUpdate();


        String sql3 = "UPDATE `b`.`xtyh` SET `balance` = balance+? WHERE `ID` = ?";
        PreparedStatement ps3 = con.prepareStatement(sql3);
        ps3.setString(1, zzjl.getZb());
        ps3.setString(2, zzjl.getSname());
        ps3.executeUpdate();

        String sql2 = "UPDATE `b`.`xtyh` SET `balance` = balance-? WHERE `name`= ?";
        PreparedStatement ps2 = con.prepareStatement(sql2);
        ps2.setString(1, zzjl.getZb());
         ps2.setString(2, zzjl.getFname());
        return ps2.executeUpdate();


    }

    @Override
    public List<Zzjl> getZzjlDao(String payname, String Sname, int a, int b) throws SQLException, IllegalAccessException {
        getBeat();
        String sql = "SELECT zzjl.id,zzjl.Fname,xtyh.`name` as Sname,zzjl.Smcp,zzjl.Ttime,zzjl.Zb FROM zzjl INNER JOIN xtyh ON xtyh.ID=zzjl.Sname WHERE zzjl.Fname LIKE ? AND xtyh.`name` LIKE ? ORDER BY Ttime DESC LIMIT ?,?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, payname);
        ps.setString(2, Sname);
        ps.setInt(3, (a - 1) * 5);
        ps.setInt(4, b);
        ResultSet rs = ps.executeQuery();
        List<Zzjl> list = new ArrayList<>();
        while (rs.next()) {
            Zzjl zzjl = new Zzjl();
            ReliesUtil.rels(rs, zzjl);
//            zzjl.setId(rs.getInt("id"));
//            zzjl.setFname(rs.getString("Fname"));
//            zzjl.setSname(rs.getString("name"));
//            zzjl.setSmcp(rs.getString("Smcp"));
//            zzjl.setTtime(rs.getString("Ttime"));
//            zzjl.setZb(rs.getString("Zb"));
            list.add(zzjl);
        }
        return list;
    }

    @Override
    public int updateXtyhDao(String payname, String Sname) throws SQLException {
        getBeat();
        String sql = "SELECT count(*)as size FROM zzjl INNER JOIN xtyh ON xtyh.ID=zzjl.Sname WHERE zzjl.Fname LIKE ? AND xtyh.`name` LIKE ? ";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, "%" + payname + "%");
        ps.setString(2, "%" + Sname + "%");
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            return rs.getInt("size");
        }
        return 0;
//        ResultSet rs = ps.executeQuery();
//        List<Zzjl> list = new ArrayList<>();
//        while (rs.next()) {
//            Zzjl zzjl = new Zzjl();
//            zzjl.setId(rs.getInt("id"));
//            zzjl.setFname(rs.getString("Fname"));
//            zzjl.setSname(rs.getString("Sname"));
//            zzjl.setSmcp(rs.getString("Smcp"));
//            zzjl.setTtime(rs.getString("Ttime"));
//            zzjl.setZb(rs.getString("Zb"));
//            list.add(zzjl);
//        }
//        return list.size();
    }
}
